package com.zhanghe.study.jdbc;

import java.sql.*;

public class DbUtils {

    // 驱动
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    // 地址
    private static final String URL = "jdbc:mysql://localhost:3306/test";
    //用户名
    private static final String USER_NAME = "root";
    // 密码
    private static final String PSW = "123456";

    /**
     *  获取连接
      */
    public static Connection getConnection(){
        Connection conn = null;
        try {
            Class.forName(DRIVER);
            conn = DriverManager.getConnection(URL,USER_NAME,PSW);

        } catch (ClassNotFoundException e) {
            System.out.println("加载驱动失败，请检查是否引入Jar包或者驱动名称是否正确");
            throw new RuntimeException("加载驱动失败，请检查是否引入Jar包或者驱动名称是否正确",e);
        } catch (SQLException throwables) {
            System.out.println("连接数据库失败，请检查数据库地址，用户名，密码是否正确");
            throw new RuntimeException("连接数据库失败，请检查数据库地址，用户名，密码是否正确",throwables);
        }
        return conn;
    }

    /**
     * 关闭连接
     * @param conn
     */
    public static void close(Connection conn,Statement statement){
        if(statement != null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

    }

    /**
     * 插入操作
     * @param sql
     */
    public static void doInsert(String sql){
        Connection conn = getConnection();
        Statement statement = null;

        try {
            statement = conn.createStatement();
            int result = statement.executeUpdate(sql);
            System.out.println(sql+"执行成功，插入"+result+"条数据");
        } catch (SQLException e) {
            throw new RuntimeException("执行失败",e);
        } finally {
            close(conn,statement);
        }

    }

    /**
     * 修改操作
     * @param sql
     */
    public static void doUpdate(String sql){
        Connection conn = getConnection();
        Statement statement = null;

        try {
            statement = conn.createStatement();
            int result = statement.executeUpdate(sql);
            System.out.println(sql+"执行成功，修改"+result+"条数据");
        } catch (SQLException e) {
            throw new RuntimeException("执行失败",e);
        } finally {
            close(conn,statement);
        }
    }

    /**
     * 删除操作
     * @param sql
     */
    public static void doDelete(String sql){
        Connection conn = getConnection();
        Statement statement = null;

        try {
            statement = conn.createStatement();
            int result = statement.executeUpdate(sql);
            System.out.println(sql+"执行成功，删除"+result+"条数据");
        } catch (SQLException e) {
            throw new RuntimeException("执行失败",e);
        } finally {
            close(conn,statement);
        }
    }

    /**
     * 预编译插入
     * @param name
     */
    public static void doPreparedInsert(String name){
        Connection conn = getConnection();
        PreparedStatement statement = null;

        try {
            String sql = "insert into user (name) values (?)";
            statement = conn.prepareStatement(sql);
//            conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            statement.setString(1,name);
            int result = statement.executeUpdate();
            System.out.println(sql+"执行成功，插入"+result+"条数据");
        } catch (SQLException e) {
            throw new RuntimeException("执行失败",e);
        } finally {
            close(conn,statement);
        }
    }

    public static void doQuery(){
        String sql = "select name aa from user";
        Connection conn = getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = conn.prepareStatement(sql);
            ResultSetMetaData metaData = statement.getMetaData();
            // 有多少列
            int num = metaData.getColumnCount();
            for(int i = 1;i<=num;i++){
                // 获取列名称和类型
                System.out.println(metaData.getColumnName(i)+","+metaData.getColumnTypeName(i)+","+metaData.getColumnLabel(i));
            }
            resultSet = statement.executeQuery();
            // resultSet.next 方法  将光标向前移动一行，最初为第一行之前，第一次调用使得第一行为当前行
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                System.out.println("查询到id为"+id+",name为"+name+"的记录");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if(resultSet != null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            close(conn,statement);
        }
    }

    public static void doBatchInsert(String sql){
        Connection conn = getConnection();
        PreparedStatement statement = null;
        try {
            statement = conn.prepareStatement(sql);
            for(int i = 0;i<1000;i++){
                statement.setString(1,"张三"+i);
                statement.addBatch();
            }
            statement.executeBatch();
            statement.clearBatch();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(conn,statement);
        }

    }

    public static void main(String[] args) {
//        String sql = "insert into user (name) values ('张三')";
//        doInsert(sql);

//        String sql = "update user set name = '李四' where id = 1";
//        doUpdate(sql);

//        String sql = "delete from user where id = 1";
//        doDelete(sql);
//        doPreparedInsert("张三");
//        doQuery();
        String sql = "insert into users (u_name) values (?)";
        long cur = System.currentTimeMillis();
        doBatchInsert(sql);
        System.out.println(System.currentTimeMillis() - cur);
    }
}
